﻿using System;
using System.Collections.Generic;
using System.Linq;
using ControleRepresentante.Logic.Models;
using ControleRepresentante.Logic.Statistics;
using NHibernate;
using NHibernate.Criterion;

namespace ControleRepresentante.Logic.Repositories
{
    public interface IRepresentanteRepository : IRepository<Representante, StatisticsBase>
    {
        void UpdateRepresentanteInfo(RepresentanteInfo representanteInfo);
    }

    public class RepresentanteRepository : RepositoryBase<Representante, StatisticsBase, RepresentanteError>, IRepresentanteRepository
    {
        public RepresentanteRepository(IRepositoryConfiguration repositoryConfiguration, ISession session)
            : base(repositoryConfiguration, session)
        {
        }

        public override Representante GetModelById(Guid id)
        {
            EnsureIsAdmin();
            using (Session.BeginTransaction())
            {
                return Session.Get<Representante>(id);
            }
        }

        public override void RemoveModelById(Guid id)
        {
            EnsureIsAdmin();
            using (ITransaction transaction = Session.BeginTransaction())
            {
                Representante representante = Session.Get<Representante>(id);
                Session.Delete(representante);
                transaction.Commit();
            }
        }

        public void UpdateRepresentanteInfo(RepresentanteInfo representanteInfo)
        {
            EnsureIsRepresentante();
            if (representanteInfo.Representante.Id != GetRepresentanteId())
            {
                throw new RestrictedAreaException();
            }

            using (ITransaction transaction = Session.BeginTransaction())
            {
                Session.Update(representanteInfo);
                transaction.Commit();
            }
        }

        public override IEnumerable<Representante> GetModels(out StatisticsBase statistics, int page = -1)
        {
            EnsureIsAdmin();
            IQueryOver<Representante, Representante> queryOver = Session.QueryOver<Representante>().OrderBy(x => x.Nome).Asc;
            int modelCount;
            IEnumerable<Representante> futurePaging = FuturePaging(queryOver, page, out modelCount);
            SetProperties(futurePaging);
            statistics = new StatisticsBase(modelCount);
            return futurePaging;
        }

        public override void AddModel(Representante representante)
        {
            EnsureIsAdmin();

            if (Session.QueryOver<Representante>()
                .Where(x => x.Nome == representante.Nome)
                .RowCount() != 0)
            {
                ThrowRepositoryException(RepresentanteError.DuplicatedNome);
            }

            representante.RepresentanteInfo.NomePedido = representante.Nome;

            using (ITransaction transaction = Session.BeginTransaction())
            {
                Session.Save(representante);
                transaction.Commit();
            }
        }

        public override void UpdateModel(Representante model)
        {
            EnsureIsAdmin();

            if (Session.QueryOver<Representante>()
                .Where(x => x.Nome == model.Nome)
                .And(x => x.Id != model.Id)
                .RowCount() != 0)
            {
                ThrowRepositoryException(RepresentanteError.DuplicatedNome);
            }

            using (ITransaction transaction = Session.BeginTransaction())
            {
                Session.Update(model);
                transaction.Commit();
            }
        }

        public override IEnumerable<Representante> GetModelsByIds(IEnumerable<Guid> ids)
        {
            IEnumerable<Representante> representantes = Session.QueryOver<Representante>()
                .Where(x => x.Id.IsIn(ids.ToArray()))
                .List();
            SetProperties(representantes);
            return representantes;
        }

        private void SetProperties(IEnumerable<Representante> representantes)
        {
            if (representantes.Any())
            {
                Dictionary<Guid, object[]> queryResultsA = Session.CreateSQLQuery(@"
                    SELECT Representante.Id, TotalCliente, TotalVendedor, TotalFabrica FROM Representante

                    LEFT JOIN (SELECT RepresentanteId, COUNT(*) TotalCliente FROM Cliente
                    INNER JOIN AttributeHolder ON UsuariosHolderId = Id
                    GROUP BY RepresentanteId) QC ON Representante.Id = QC.RepresentanteId

                    LEFT JOIN (SELECT RepresentanteId, COUNT(*) TotalVendedor FROM Vendedor
                    INNER JOIN AttributeHolder ON UsuariosHolderId = Id
                    GROUP BY RepresentanteId) QV ON Representante.Id = QV.RepresentanteId

                    LEFT JOIN (SELECT RepresentanteId, COUNT(*) TotalFabrica FROM Fabrica
                    INNER JOIN AttributeHolder ON UsuariosHolderId = Id
                    GROUP BY RepresentanteId) QF ON Representante.Id = QF.RepresentanteId")
                    .AddScalar("Id", NHibernateUtil.Guid)
                    .AddScalar("TotalCliente", NHibernateUtil.Int32)
                    .AddScalar("TotalVendedor", NHibernateUtil.Int32)
                    .AddScalar("TotalFabrica", NHibernateUtil.Int32)
                    .List()
                    .Cast<object[]>()
                    .ToDictionary(x => (Guid)x[0], x => x);

                Dictionary<Guid, object[]> queryResultsB = Session.CreateSQLQuery(@"
                    SELECT Representante.Id, TotalTransportadora, TotalPedido, LastPedido FROM Representante

                    LEFT JOIN (SELECT RepresentanteId, COUNT(*) TotalTransportadora FROM Transportadora
                    INNER JOIN AttributeHolder ON UsuariosHolderId = Id
                    GROUP BY RepresentanteId) QT ON Representante.Id = QT.RepresentanteId

                    LEFT JOIN (SELECT RepresentanteId, COUNT(*) TotalPedido FROM Pedido
                    INNER JOIN AttributeHolder ON AttributeHolderId = Id
                    GROUP BY RepresentanteId) QP ON Representante.Id = QP.RepresentanteId

                    LEFT JOIN (SELECT RepresentanteId, MAX(Data) LastPedido FROM Pedido
                    INNER JOIN AttributeHolder ON AttributeHolderId = Id
                    GROUP BY RepresentanteId) QLP ON Representante.Id = QLP.RepresentanteId")
                    .AddScalar("Id", NHibernateUtil.Guid)
                    .AddScalar("TotalTransportadora", NHibernateUtil.Int32)
                    .AddScalar("TotalPedido", NHibernateUtil.Int32)
                    .AddScalar("LastPedido", NHibernateUtil.DateTime)
                    .List()
                    .Cast<object[]>()
                    .ToDictionary(x => (Guid)x[0], x => x);
                foreach (Representante representante in representantes)
                {
                    object[] queryResultA = queryResultsA[representante.Id];
                    object[] queryResultB = queryResultsB[representante.Id];
                    representante.NumeroDeClientes = queryResultA[1] == null ? 0 : (int)queryResultA[1];
                    representante.NumeroDeVendedores = queryResultA[2] == null ? 0 : (int)queryResultA[2];
                    representante.NumeroDeFabricas = queryResultA[3] == null ? 0 : (int)queryResultA[3];
                    representante.NumeroDeTransportadoras = queryResultB[1] == null ? 0 : (int)queryResultB[1];
                    representante.NumeroDePedidos = queryResultB[2] == null ? 0 : (int)queryResultB[2];
                    representante.LastPedido = (DateTime?)queryResultB[3];
                }
            }
        }
    }

    public enum RepresentanteError
    {
        DuplicatedNome,
        EmptyNomePedido
    }
}
